package com.troyhigh.njrotc.admintrackerold;

import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Hashtable;

public class SQLDatabase implements Database
{
    private static final int STU_TBL_FIR = 5; //first index of data on students
    private static final int EVT_TBL_FIR = 4; //first index of data on events
    
    private static boolean connected = false;  
    private static Connection conn = null;
    
    public void connect()
    {
        try
        {
            System.out.println("Attempting connection...");
            Class.forName("com.mysql.jdbc.Driver").newInstance(); 
            conn = DriverManager.getConnection("jdbc:mysql://localhost/test?" 
                    + "user=brother&password=BROroster2008"); 
            Statement use = conn.createStatement();
            use.execute("USE admintracker");
            System.out.println("Connection successful.");
            connected = true;
        }
        catch (SQLException e)
        {
            System.out.println("SQL Exception!");
            e.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("JDBC Driver not found!");
            e.printStackTrace();
        }
        catch (Exception e)
        {
            System.out.println("Other Exception!");
            e.printStackTrace();
        }
    }
    
    public boolean isConnected()
    {
        return connected;
    }
    
    public void disconnect()
    {
        try
        {
            System.out.println("Closing connection...");
            if(!conn.isClosed())
            {
                if(!conn.getAutoCommit()) conn.commit();
                conn.close();
            }
            System.out.println("Connection successfully closed.");
        }
        catch (SQLException e)
        {
            System.out.println("SQL Exception!");
            e.printStackTrace();
        }
        catch (Exception e)
        {
            System.out.println("Other Exception!");
            e.printStackTrace();
        }
        connected = false;
    }
    
    public void committ()
    {
        //because MySQL committs every change, this method is not needed.
    }
    
    public ArrayList<Event> getEvents()
    {
        if(connected)
        {
            ArrayList<Event> events = new ArrayList<Event>();
            try
            {
                Statement getEventsSM = conn.createStatement();
                ResultSet eventSet = getEventsSM.executeQuery("SELECT * FROM EVENTS ORDER BY started");
                events = buildEventSet(eventSet);
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
            return events;
        }
        return null;
    }
    
    private Event parseEvent(String name, String date, String category, String[] properties)
    {
        String n = (name == null ? "" : name);
        Date d = (date == null ? ((Date)new java.util.Date()) : Date.valueOf(date));
        Category c = Category.parseCategory(category == null ? "NONE" : category);
        String[] s = properties;
        Properties p = new Properties();
        if(s != null && s.length != 0 && s.length % 2 == 0)
        {
            for(int i = 0; i < s.length; i+=2)
            {
                p.setProperty(s[i], s[i+1]);
            }
        }
        Event result = new Event(n, d, c, p);      
        result.loadTable(getRoster(result));
        return result;
    }
    
    private ArrayList<Event> buildEventSet(ResultSet rs)
    {
        ArrayList<Event> e = new ArrayList<Event>();
        if(rs == null) return e;
        try
        {
            rs.first();
            if(rs.isAfterLast()) return null;
            while(!rs.isAfterLast())
            {
                String n = rs.getString(1);
                String d = rs.getString(2);
                String c = rs.getString(3);
                int width = (1 + rs.getMetaData().getColumnCount() - EVT_TBL_FIR);
                String[] p = new String[2*width];
                for(int i = EVT_TBL_FIR; i - EVT_TBL_FIR < width * 2; i+=2)
                {
                    p[i-EVT_TBL_FIR+1] = rs.getString((i+EVT_TBL_FIR)/2);
                    p[(i-EVT_TBL_FIR)] = rs.getMetaData().getColumnName((i+EVT_TBL_FIR)/2);
                }       
                rs.next();
                e.add(parseEvent(n, d, c, p));
            }
        }
        catch (SQLException sqle)
        {
            System.out.println("SQL Exception!");
            sqle.printStackTrace();
        }
        return e;
    }
    
    public ArrayList<Student> getStudents()
    {
        if(connected)
        {
            ArrayList<Student> students = new ArrayList<Student>();
            try
            {
                Statement getStudentsSM = conn.createStatement();
                ResultSet studentSet = getStudentsSM.executeQuery("SELECT * FROM STUDENTS");
                students = buildStudentSet(studentSet);
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
            return students;
        }
        return null;
    }
    
    private Student parseStudent(int g, String l, String f, int id, String[] properties, RibbonData r)
    {
        int gr = (g < 0 ? -1 : g); // g stand for group which means platoon
        String la = (l == null ? "" : l);
        String fi = (f == null ? "" : f);
        int id2 = (id <= 0 ? -1 : id);
        String[] s = properties;
        Properties p = new Properties();
        if(s != null && s.length != 0 && s.length % 2 == 0)
        {
            for(int i = 0; i < s.length; i+=2)
            {
                p.setProperty(s[i], s[i+1]);
            }
        }
        return new Student(gr, la, fi, id2, p, r);          
    }
    
    private ArrayList<Student> buildStudentSet(ResultSet rs)
    {
        ArrayList<Student> s = new ArrayList<Student>();
        if(rs == null) return s;
        try
        {
            rs.first();
            if(rs.isAfterLast()) return s;
            do
            {
                int g = rs.getInt(1);
                String l = rs.getString(2);
                String f = rs.getString(3);
                int id = rs.getInt(4);     
                int width = (1 + rs.getMetaData().getColumnCount() - STU_TBL_FIR);
                String[] p = new String[2*width];
                for(int i = STU_TBL_FIR; i - STU_TBL_FIR < width * 2; i+=2)
                {
                    p[i+1-STU_TBL_FIR] = rs.getString((i+STU_TBL_FIR)/2);
                    p[i-STU_TBL_FIR] = rs.getMetaData().getColumnName((i+STU_TBL_FIR)/2);
                }
                RibbonData r = getAward(id);
                if (r == null) r = new RibbonData(id);
                s.add(parseStudent(g, l, f, id, p, r));
                if(!rs.isLast()) 
                {
                    rs.next();
                }
            } while (!rs.isLast());
        }
        catch (SQLException sqle)
        {
            System.out.println("SQL Exception!");
            sqle.printStackTrace();
        }
        return s;
    }
    
    public boolean studentExists(Student s)
    {
        return getStudent(s.getLastName(), s.getFirstName(), s.getID()) != null;
    }
    
    public ArrayList<Student> getStudents(String l2, String f2, int id)
    {
        if(connected)
        {
            String l = (l2 == null ? "" : l2);
            String f = (f2 == null ? "" : f2);
            String query = "SELECT * FROM STUDENTS";
            if(!(l.equals("") && f.equals("") && id < 0))
            {
                query += " WHERE ";
            }
            else return null;
            query += (!l.equals("")) ? ("lastName = \"" + l + "\"") : ("");
            query += (!l.equals("") && !f.equals("")) ? " AND " : "";
            query += (!f.equals("")) ? ("firstName = \"" + f + "\"") : ("");
            query += ((!l.equals("") || !f.equals("")) && id > 0) ? " AND " : "";
            query += (id > 0) ? ("id = " + id) : "";
            try
            {
                Statement getStudentSM = conn.createStatement();
                ResultSet rs = getStudentSM.executeQuery(query);
                if(rs.isAfterLast())
                    return null;
                return buildStudentSet(rs);
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
        return null;
    }
    
    public ArrayList<RibbonData> getAwards()
    {
        if(connected)
        {
            String query = "SELECT * FROM RIBBONS";
            try
            {
                Statement getAwardSM = conn.createStatement();
                ResultSet rs = getAwardSM.executeQuery(query);
                if(rs.isAfterLast())
                    return null;
                return buildAwardSet(rs);
            }
            catch(SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
        return null;
    }
    
    public RibbonData getAward(int id)
    {
        ArrayList<RibbonData> r = getAwards(id);
        if(r != null && r.size() >= 1)
            return getAwards(id).get(0);
        return null;
    }
    
    public void setAwards(ArrayList<RibbonData> r)
    {
        for(RibbonData rd : r)
            setAward(rd);
    }
    
    public void setAward(RibbonData r)
    {
        if(connected && r != null && r.getID() > 0)
        {
            try
            {
                if(awaExists(r))
                {
                    String info = r.stringValues();
                    String update = "UPDATE ribbons SET "+info+" WHERE id = " + r.getID();
                    System.out.println("sql: " + update);
                    Statement updateEvent = conn.createStatement();
                    updateEvent.executeUpdate(update);             
                }
                else
                {
                    String columnValues = RibbonData.formattedLabels();
                    String awardValues = r.valuesOnly();
                    String insert = "INSERT INTO ribbons " + columnValues +
                                    " VALUES("+ r.getID() + ", " +awardValues+")";
                    System.out.println("sql: " + insert);
                    Statement insertAward = conn.createStatement();
                    insertAward.execute(insert);
                }
            }
            catch(SQLException se)
            {
                System.out.println("SQL Exception!");
                se.printStackTrace();
            }
        }      
    }
    
    public boolean awaExists(RibbonData r)
    {
        if(connected)
        {
            if(r == null) return false;
            String query = "SELECT * FROM RIBBONS";
            if(r.getID() > 0)
            {
                query += " WHERE ";
            }
            else return false;
            query += " id = " + r.getID();
            try
            {
                Statement getStudentSM = conn.createStatement();
                ResultSet rs = getStudentSM.executeQuery(query);
                return rs.first();
            }
            catch (Exception e)
            {
                System.out.println("SQL Exception!");
                e.printStackTrace();
            }
        }
        return false;
    }
    
    public ArrayList<RibbonData> getAwards(int id)
    {
        if(connected)
        {
            String query = "SELECT * FROM RIBBONS";
            if(id > 0)
            {
                query += " WHERE ";
            }
            else return null;
            query += "id = " + id;
            try
            {
                Statement getAwardSM = conn.createStatement();
                ResultSet rs = getAwardSM.executeQuery(query);
                if(rs.isAfterLast())
                    return null;
                return buildAwardSet(rs);
            }
            catch(SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
        return null;
    }
    
    private ArrayList<RibbonData> buildAwardSet(ResultSet rs)
    {
        ArrayList<RibbonData> rd = new ArrayList<RibbonData>();
        if(rs == null) return rd;
        try
        {
            rs.first();
            if(rs.isAfterLast()) return rd;
            do
            {
                int[] arr = new int[25];
                for(int i = 1; i <= 25; i++)
                {
                    arr[i-1] = rs.getInt(i+1);
                }
                rd.add(new RibbonData(rs.getInt(1), arr));
                if(!rs.isLast())
                {
                    rs.next();
                }
            }
            while(!rs.isLast());
        }
        catch (SQLException sqle)
        {
            System.out.println("SQL Exception!");
            sqle.printStackTrace();
        }
        return rd;
    }
            
    
    public Student getStudent(String l, String f, int id)
    {
        ArrayList<Student> s = getStudents(l, f, id);
        if(s != null && s.size() > 0)
            return s.get(0);
        return null;
    }
    
    public void removeStudent(String l, String f, int id)
    {
        if(connected)
        {
            String ln = (l == null ? "" : l);
            String fn = (f == null ? "" : f);
            String query = "DELETE FROM STUDENTS";
            if(!(ln.equals("") && fn.equals("") && id < 0))
            {
                query += " WHERE ";
            }
            query += (!ln.equals("")) ? ("lastName = \"" + ln + "\"") : ("");
            query += (!ln.equals("") && !fn.equals("")) ? " AND " : "";
            query += (!fn.equals("")) ? ("firstName = \"" + fn + "\"") : ("");
            query += ((!ln.equals("") || !fn.equals("")) && id > 0) ? " AND " : "";
            query += (id > 0) ? ("id = " + id) : "";
            query += " LIMIT 1";
            try
            {
                Statement rmStudent = conn.createStatement();
                rmStudent.execute(query);
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
    }
    
    public boolean eventExists(Event e)
    {
        return getEvent(e.getName(), e.getDate()) != null;
    }
                
    public ArrayList<Event> getEvents(String name1, Date d1)
    {
        if(connected)
        {
            String name = (name1 == null ? "" : name1);
            Date d = (d1 == null ? ((Date)new java.util.Date()) : d1);
            String query = "SELECT * FROM EVENTS";
            if(!(name.equals("") && d == null))
            {
                query += " WHERE ";
            }
            else return null;
            query += (!name.equals("")) ? ("name = \"" + name + "\"") : ("");
            query += (!name.equals("") && d != null) ? " AND " : "";
            query += (d != null) ? ("started = '" + d.toString() + "'") : ("");
            try
            {
                Statement getEventSM = conn.createStatement();
                ResultSet rs = getEventSM.executeQuery(query);
                if(rs.isAfterLast())
                    return null;
                ArrayList<Event> eventSet = buildEventSet(rs);
                for(Event e : eventSet)
                {
                    e.loadTable(getRoster(e));
                }
                return eventSet;
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
        return null;
    }
    
    public void removeEvent(String n, Date d)
    {
        if(connected)
        {
            String query = "DELETE FROM EVENTS";
            if(!(n == null && n.equals("") && d == null))
            {
                query += " WHERE ";
            }
            else return;
            query += (!n.equals("")) ? ("name = \"" + n + "\"") : ("");
            query += (!n.equals("") && d != null) ? " AND " : "";
            query += (d != null) ? ("started = '" + d.toString() + "'") : ("");
            query += " LIMIT 1";
            try
            {
                Statement rmEvent = conn.createStatement();
                rmEvent.execute(query);
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
    }
    
    public Event getEvent(String n, Date d)
    {
        ArrayList<Event> e = getEvents(n, d);
        if(e != null && e.size() > 0)
             return e.get(0);
        return null;
    }
    
    private boolean stuExists(String l, String f, int id)
    {
        if(connected)
        {
            String query = "SELECT * FROM STUDENTS";
            if(!(l.equals("") && f.equals("") && id < 0))
            {
                query += " WHERE ";
            }
            else return false;
            query += (!l.equals("")) ? ("lastName = \"" + l + "\"") : ("");
            query += (!l.equals("") && !f.equals("")) ? " AND " : "";
            query += (!f.equals("")) ? ("firstName = \"" + f + "\"") : ("");
            query += ((!l.equals("") || !f.equals("")) && id > 0) ? " AND " : "";
            query += (id > 0) ? ("id = " + id) : "";
            try
            {
                Statement getStudentSM = conn.createStatement();
                ResultSet rs = getStudentSM.executeQuery(query);
                return rs.first();
            }
            catch (Exception e)
            {
                System.out.println("SQL Exception!");
                e.printStackTrace();
            }
        }
        return false;
    }
    
    private boolean eveExists(String name, Date d)
    {
        if(connected)
        {
            String query = "SELECT * FROM EVENTS";
            if(!(name.equals("") && d == null))
            {
                query += " WHERE ";
            }
            else return false;
            query += (!name.equals("")) ? ("name = \"" + name + "\"") : ("");
            query += (!name.equals("") && d != null) ? " AND " : "";
            query += (d != null) ? ("started = '" + d.toString() + "'") : ("");
            try
            {
                Statement getEventSM = conn.createStatement();
                ResultSet rs = getEventSM.executeQuery(query);
                return rs.first();
            }
            catch(Exception ex)
            {
                System.out.println("SQL Exception!");
                ex.printStackTrace();
            }
        }
        return false;
    }
    
    public void setEvent(Event e)
    {
        if(connected && (!e.getName().equals("")) && e.getDate() != null)
        {
            try
            {
                if(eveExists(e.getName(), e.getDate()))
                {
                    String info = formEventInfo(e);
                    String update = "UPDATE events SET "+info+" WHERE name = '"+e.getName()+
                                    "' AND started = '"+e.getDate().toString()+"'";
                    Statement updateEvent = conn.createStatement();
                    updateEvent.executeUpdate(update);             
                }
                else
                {
                    String columnValues = "(name, started, type, counting, countingvalue)";
                    String eventValues = formEventValues(e);
                    String insert = "INSERT INTO events " + columnValues +
                                    " VALUES"+eventValues;
                    Statement insertEvent = conn.createStatement();
                    insertEvent.execute(insert);
                }
            }
            catch(SQLException se)
            {
                System.out.println("SQL Exception!");
                se.printStackTrace();
            }
        }
    }
    
    private String formEventInfo(Event e)
    {
        String name = e.getName();
        String date = e.getDate().toString();
        String cate = e.getCategory().toString();
        String coun = e.getProperties().getProperty("counting");
        String valu = e.getProperties().getProperty("countingvalue");
        return "name = '"+name+"',"+
               " started = '"+date+"'," +
               " type = '" + cate + "',"+
               " counting = '"+coun+"',"+
               " countingvalue = '"+valu+"'";
    }
    
    private String formEventValues(Event e)
    {
        String name = e.getName();
        String date = e.getDate().toString();
        String cate = e.getCategory().toString();
        String coun = e.getProperties().getProperty("counting");
        String valu = e.getProperties().getProperty("countingvalue");
        return "('"+name+"', '"+date+"', '"+cate+"', '"+coun+"', " + valu+")";
    }
    
    public void setStudent(Student s)
    {
        if(connected && s.getID() > 0)
        {
            try
            {
                if(stuExists(s.getLastName(), s.getFirstName(), s.getID()))
                {
                    String info = formStudentInfo(s);
                    String update = "UPDATE students SET "+info+" WHERE id="+s.getID();
                    Statement updateStudent = conn.createStatement();
                    updateStudent.executeUpdate(update);
                }
                else
                {
                    String columnValues = "(platoon,lastname,firstname,id,gender,grade,nsyear)";
                    String studentValues = formStudentValues(s);
                    String insert = "INSERT INTO students " + columnValues +
                                    " VALUES"+studentValues;
                    Statement insertStudent = conn.createStatement();
                    insertStudent.execute(insert);
                }
                setAward(s.getRibbons());
            }
            catch (SQLException e)
            {
                System.out.println("SQL Exception!");
                e.printStackTrace();
            }
        }
    }
    
    private String formStudentInfo(Student s)
    {
        String gender = s.getProperties().getProperty("gender");
        String grade = s.getProperties().getProperty("grade");
        String nsyear = s.getProperties().getProperty("nsyear");
        return "platoon ="+s.getGroup() + ", " +
               "lastname =\"" + s.getLastName() + "\", " +
               "firstname =\"" + s.getFirstName() + "\", " +
               "id ="+s.getID() + ", " +
               "gender =\"" + gender.charAt(0) + "\", " +
               "grade =" + grade + ", " +
               "nsyear =" + nsyear + "";
    }
    
    private String formStudentValues(Student s)
    {
        String gender = s.getProperties().getProperty("gender").substring(0,1);
        String grade = s.getProperties().getProperty("grade");
        String nsyear = s.getProperties().getProperty("nsyear");
        return "("+s.getGroup()+",'"+s.getLastName()+"','"+s.getFirstName()+
               "',"+s.getID()+",'"+gender+"',"+grade+","+nsyear+")";
    } 
    
    public void setEvents(ArrayList<Event> e)
    {
        for(Event event : e)
        {
            setRoster(event);
            setEvent(event);
        }
    }
    
    public void setStudents(ArrayList<Student> s)
    {
        for(Student student : s)
            setStudent(student);
    }
    
    private boolean rosterExists(Event e)
    {
        if(connected)
        {
            if(e == null) return false;
            try
            {
                Statement tbls = conn.createStatement();
                ResultSet rs = tbls.executeQuery("SHOW TABLES");
                if(rs.isAfterLast())
                    return false;
                rs.first();
                while(!rs.isAfterLast())
                {
                    if(rs.getString(1).equals(e.PID()))
                        return true;
                    rs.next();
                }
                return false;
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
        return false;
    }
    
    public Hashtable<Integer, Integer> getRoster(Event e)
    {
        if(connected)
        {
             if(e == null) return null;
             try
             {
                if(!rosterExists(e)) //if the event doesn't have an entry...
                {
                    Statement nt = conn.createStatement();
                    nt.execute("CREATE TABLE " + e.PID() + " LIKE roster");
                    return new Hashtable<Integer, Integer>();
                }
                Statement getRoster = conn.createStatement();
                ResultSet rs = getRoster.executeQuery("SELECT * FROM " + e.PID());
                Hashtable<Integer, Integer> roster = new Hashtable<Integer, Integer>();
                if(rs.first())
                {
                    while(!rs.isAfterLast())
                    {
                        roster.put(new Integer(rs.getInt(1)), new Integer(rs.getInt(2)));
                        rs.next();
                    }
                }
                return roster;
             }
             catch (SQLException sqle)
             {
                 System.out.println("SQL Exception!");
                 sqle.printStackTrace();
             }
        }
        return null;
    }
    
    public void setRoster(Event e)
    {
        if(connected)
        {
            try
            {
                Statement nt = conn.createStatement();
                if(rosterExists(e)) nt.execute("DROP TABLE " + e.PID());
                nt.execute("CREATE TABLE " + e.PID() + " LIKE roster");
                
                ArrayList<Integer> stus = e.getIDs();
                for(Integer i : stus)
                {
                    Student s = getStudent("", "", i.intValue());
                    Statement insert = conn.createStatement();
                    int id = s.getID();
                    int va = e.get(s);
                    String state = "INSERT INTO " + e.PID() + " (id, value)"+
                                   " VALUES(" + id + ", " + va + ")";
                    insert.execute(state);
                }
            }
            catch (SQLException sqle)
            {
                System.out.println("SQL Exception!");
                sqle.printStackTrace();
            }
        }
    }
}
